package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import beans.Account;
import beans.Database;
import beans.Thread;

public class ThreadDAO {

	public static Thread selectById(int id) {
		Thread thread = null;
		Connection conn = null;
		PreparedStatement pre = null;
		ResultSet rs = null;
		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("SELECT thread.*, (SELECT count(*) FROM comment WHERE comment.thread_id=thread.id) AS reply FROM thread WHERE thread.id=?");
			pre.setInt(1, id);
			rs = pre.executeQuery();

			if (rs.next()) {
				Account acc = AccountDAO.selectSumaryById(rs.getInt("user_id"));
				thread = new Thread(rs.getInt("id"), rs.getInt("box_id"), acc,
						rs.getString("title"), rs.getString("content"),
						rs.getInt("see"), rs.getInt("reply"),
						rs.getInt("state"), rs.getDate("posted"));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pre.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return thread;
	}

	public static void updateSee(int threadId) {
		Connection conn = null;
		PreparedStatement pre = null;

		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("UPDATE thread SET see=see+1 WHERE id=?");
			pre.setInt(1, threadId);
			pre.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static boolean deleteById(int threadId) {
		Connection conn = null;
		PreparedStatement pre = null;

		try {
			conn = Database.connect();
			pre = conn.prepareStatement("DELETE FROM thread WHERE id=?");
			pre.setInt(1, threadId);
			int row = pre.executeUpdate();

			if (row > 0)
				return true;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public static boolean settings(String sql, int threadId) {
		Connection conn = null;
		PreparedStatement pre = null;

		try {
			conn = Database.connect();
			pre = conn.prepareStatement(sql);
			pre.setInt(1, threadId);

			int row = pre.executeUpdate();
			if (row > 0)
				return true;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public static boolean update(int threadId, String title, String content) {
		Connection conn = null;
		PreparedStatement pre = null;

		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("UPDATE thread SET title=?, content=? WHERE id=?");
			pre.setString(1, title);
			pre.setString(2, content);
			pre.setInt(3, threadId);

			int row = pre.executeUpdate();
			if (row > 0)
				return true;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * use select thread to edit
	 * 
	 * @param threadId
	 * @return
	 */
	public static Thread selectSumaryThread(int threadId) {
		Thread thread = null;
		Connection conn = null;
		PreparedStatement pre = null;
		ResultSet rs = null;

		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("SELECT id, title, content FROM thread WHERE id=?");
			pre.setInt(1, threadId);
			rs = pre.executeQuery();

			if (rs.next()) {
				thread = new Thread(threadId, rs.getString("title"),
						rs.getString("content"));
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return thread;
	}

	// select new thread in box
	public static ArrayList<Thread> selectNoteThread(int boxId) {
		ArrayList<Thread> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement pre = null;
		ResultSet rs = null;

		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("SELECT thread.*, (SELECT count(*) FROM comment WHERE comment.thread_id=thread.id) AS reply FROM thread INNER JOIN member ON thread.user_id=member.id WHERE thread.box_id=? AND thread.state=1 || thread.state=2");
			pre.setInt(1, boxId);
			rs = pre.executeQuery();

			while (rs.next()) {
				Account acc = AccountDAO.selectSumaryById(rs.getInt("user_id"));
				Thread thread = new Thread(rs.getInt("id"), acc,
						rs.getString("title"), rs.getInt("see"),
						rs.getInt("reply"));
				thread.setState(rs.getInt("state"));
				thread.setBoxID(rs.getInt("box_id"));
				list.add(thread);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}

	public static ArrayList<Thread> selectNormalThread(int boxId, int min,
			int max) {
		ArrayList<Thread> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement pre = null;
		ResultSet rs = null;

		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("SELECT thread.*, (SELECT count(*) FROM comment WHERE thread_id=thread.id) AS reply FROM thread INNER JOIN member ON thread.user_id = member.id WHERE thread.box_id=? AND thread.state!=1 && thread.state!=2 ORDER BY thread.time DESC LIMIT ?,?");
			pre.setInt(1, boxId);
			pre.setInt(2, min);
			pre.setInt(3, max);
			rs = pre.executeQuery();

			while (rs.next()) {
				Account acc = AccountDAO.selectSumaryById(rs.getInt("user_id"));
				Thread thread = new Thread(rs.getInt("id"), acc,
						rs.getString("title"), rs.getInt("see"),
						rs.getInt("reply"));
				thread.setState(rs.getInt("state"));
				thread.setBoxID(rs.getInt("box_id"));
				list.add(thread);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}

	// select new thread in forum
	public static ArrayList<Thread> selectNewThread(int num) {
		ArrayList<Thread> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement pre = null;
		ResultSet rs = null;

		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("(SELECT thread.*, (SELECT count(*) FROM comment WHERE comment.thread_id=thread.id) AS reply FROM (thread INNER JOIN member ON thread.user_id=member.id) ORDER BY thread.time DESC LIMIT ?) ORDER BY time ASC");
			pre.setInt(1, num);
			rs = pre.executeQuery();

			while (rs.next()) {
				Account acc = AccountDAO.selectSumaryById(rs.getInt("user_id"));
				Thread thread = new Thread(rs.getInt("id"), acc,
						rs.getString("title"), rs.getInt("see"),
						rs.getInt("reply"));
				thread.setState(rs.getInt("state"));
				thread.setBoxID(rs.getInt("box_id"));
				list.add(thread);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}

	// select num thread in box form forum
	public static ArrayList<Thread> selectThreadInBox(int boxId, int num) {
		ArrayList<Thread> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement pre = null;
		ResultSet rs = null;

		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("SELECT id, title, posted FROM thread WHERE box_id=? ORDER BY posted DESC LIMIT ?");
			pre.setInt(1, boxId);
			pre.setInt(2, num);
			rs = pre.executeQuery();

			while (rs.next()) {
				Thread thread = new Thread(rs.getInt("id"),
						rs.getString("title"), rs.getString("posted"));
				list.add(thread);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}

	public static boolean insert(int user_id, int boxId, String title,
			String content) {
		Connection conn = null;
		PreparedStatement pre = null;

		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("INSERT INTO thread(user_id,box_id,title,content) VALUES(?,?,?,?)");
			pre.setInt(1, user_id);
			pre.setInt(2, boxId);
			pre.setString(3, title);
			pre.setString(4, content);

			int row = pre.executeUpdate();
			if (row > 0)
				return true;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
	}
}
